package com.chinasoft.localtest;
import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvException;

import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class CsvToMysql {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/local_test";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "ccd123";
    private static final String CSV_FILE_PATH = "/mnt/Electronic_product_sales_analysis.csv";

    public static void main(String[] args) {
        // 加载 MySQL 驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        }

        // 连接数据库
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            // 创建表
            createTable(connection);

            // 读取 CSV 文件并插入数据
            insertDataFromCsv(connection);
        } catch (SQLException | IOException | CsvException e) {
            e.printStackTrace();
        }
    }

    private static void createTable(Connection connection) throws SQLException {
        String createTableSql = "CREATE TABLE IF NOT EXISTS electronic_product_sales (" +
                "id BIGINT," +
                "event_time VARCHAR(255)," +
                "order_id BIGINT," +
                "product_id BIGINT," +
                "category_id DOUBLE," +
                "category_code VARCHAR(255)," +
                "brand VARCHAR(255)," +
                "price DOUBLE," +
                "user_id DOUBLE," +
                "age INT," +
                "sex VARCHAR(10)," +
                "local VARCHAR(255)" +
                ")";
        try (Statement statement = connection.createStatement()) {
            statement.executeUpdate(createTableSql);
        }
    }

    private static void insertDataFromCsv(Connection connection) throws IOException, CsvException, SQLException {
        try (CSVReader csvReader = new CSVReader(new FileReader(CSV_FILE_PATH))) {
            String[] header = csvReader.readNext(); // 读取表头
            String[] line;
            String insertSql = "INSERT INTO electronic_product_sales (" +
                    "id, event_time, order_id, product_id, category_id, category_code, brand, price, user_id, age, sex, local" +
                    ") VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
            try (PreparedStatement preparedStatement = connection.prepareStatement(insertSql)) {
                while ((line = csvReader.readNext()) != null) {
                    preparedStatement.setLong(1, Long.parseLong(line[0]));
                    preparedStatement.setString(2, line[1]);
                    preparedStatement.setLong(3, Long.parseLong(line[2]));
                    preparedStatement.setLong(4, Long.parseLong(line[3]));
                    preparedStatement.setDouble(5, Double.parseDouble(line[4]));
                    preparedStatement.setString(6, line[5]);
                    preparedStatement.setString(7, line[6]);
                    preparedStatement.setDouble(8, Double.parseDouble(line[7]));
                    preparedStatement.setDouble(9, Double.parseDouble(line[8]));
                    preparedStatement.setInt(10, Integer.parseInt(line[9]));
                    preparedStatement.setString(11, line[10]);
                    preparedStatement.setString(12, line[11]);
                    preparedStatement.executeUpdate();
                }
            }
        }
    }
}